{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>orderID</th>\n",
       "      <th>customerID</th>\n",
       "      <th>order_date</th>\n",
       "      <th>store</th>\n",
       "      <th>product</th>\n",
       "      <th>quantity</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>O1</td>\n",
       "      <td>C1</td>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>O1</td>\n",
       "      <td>C1</td>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>O1</td>\n",
       "      <td>C1</td>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>A</td>\n",
       "      <td>C</td>\n",
       "      <td>1</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>O2</td>\n",
       "      <td>C2</td>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>B</td>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>O2</td>\n",
       "      <td>C2</td>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>B</td>\n",
       "      <td>C</td>\n",
       "      <td>1</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>O3</td>\n",
       "      <td>C3</td>\n",
       "      <td>2020-03-11</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>O3</td>\n",
       "      <td>C3</td>\n",
       "      <td>2020-03-11</td>\n",
       "      <td>A</td>\n",
       "      <td>D</td>\n",
       "      <td>1</td>\n",
       "      <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>O3</td>\n",
       "      <td>C3</td>\n",
       "      <td>2020-03-11</td>\n",
       "      <td>A</td>\n",
       "      <td>S</td>\n",
       "      <td>1</td>\n",
       "      <td>500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>O4</td>\n",
       "      <td>C4</td>\n",
       "      <td>2020-03-15</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>O4</td>\n",
       "      <td>C4</td>\n",
       "      <td>2020-03-15</td>\n",
       "      <td>A</td>\n",
       "      <td>F</td>\n",
       "      <td>2</td>\n",
       "      <td>700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>O5</td>\n",
       "      <td>C5</td>\n",
       "      <td>2020-03-16</td>\n",
       "      <td>C</td>\n",
       "      <td>C</td>\n",
       "      <td>2</td>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   orderID customerID order_date store product  quantity  price\n",
       "0       O1         C1 2020-01-09     A       A         1    100\n",
       "1       O1         C1 2020-01-09     A       B         1    300\n",
       "2       O1         C1 2020-01-09     A       C         1    200\n",
       "3       O2         C2 2020-02-01     B       B         1    300\n",
       "4       O2         C2 2020-02-01     B       C         1    200\n",
       "5       O3         C3 2020-03-11     A       A         1    100\n",
       "6       O3         C3 2020-03-11     A       D         1    150\n",
       "7       O3         C3 2020-03-11     A       S         1    500\n",
       "8       O4         C4 2020-03-15     A       B         1    300\n",
       "9       O4         C4 2020-03-15     A       F         2    700\n",
       "10      O5         C5 2020-03-16     C       C         2    400"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 读取数据集\n",
    "df1 = pd.read_excel('./示例文件.xlsx',sheet_name='data1')\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>product</th>\n",
       "      <th>category</th>\n",
       "      <th>function</th>\n",
       "      <th>retail_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>B</td>\n",
       "      <td>A</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>C</td>\n",
       "      <td>A</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>D</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>F</td>\n",
       "      <td>A</td>\n",
       "      <td>C</td>\n",
       "      <td>350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>S</td>\n",
       "      <td>C</td>\n",
       "      <td>B</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  product category function  retail_price\n",
       "0       A        A        A           100\n",
       "1       B        B        A           300\n",
       "2       C        C        A           200\n",
       "3       D        A        B           150\n",
       "4       F        A        C           350\n",
       "5       S        C        B           200"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.read_excel('./示例文件.xlsx',sheet_name='data2')\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>store</th>\n",
       "      <th>city</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>B</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>D</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E</td>\n",
       "      <td>C</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  store city region\n",
       "0     A    A      A\n",
       "1     B    B      B\n",
       "2     C    A      A\n",
       "3     D    A      A\n",
       "4     E    C      B"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = pd.read_excel('./示例文件.xlsx',sheet_name='data3')\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020 年第 1 季度的购买人数有 5 人，销售金额有 3250 元，客单价为 650.0 元/单，客单件为 2.6 件/单，人均购买频次为 1.0 次/人\n"
     ]
    }
   ],
   "source": [
    "# 计算 2020 年第 1 季度的购买人数、销售金额、客单价、客单件、人均购买频次\n",
    "con1 = df1['order_date'] >= '2020-01-01'\n",
    "con2 = df1['order_date'] <= '2020-03-31'\n",
    "df = df1[con1 & con2]\n",
    "count_customer = len((df['customerID'].unique()))\n",
    "count_order = len((df['orderID'].unique()))\n",
    "price_sum = df['price'].sum()\n",
    "quantity_sum = df['quantity'].sum()\n",
    "price_per_order = price_sum / count_order\n",
    "quantity_per_order = quantity_sum / count_order\n",
    "times_per_customer = count_order / count_customer\n",
    "print(f'2020 年第 1 季度的购买人数有 {count_customer} 人，销售金额有 {price_sum} 元，客单价为 {price_per_order} 元/单，客单件为 {quantity_per_order} 件/单，人均购买频次为 {times_per_customer} 次/人')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-05 至 2020-04 期间的复购率为 0.0%\n"
     ]
    }
   ],
   "source": [
    "# 计算品牌在 2019-05 至 2020-04 期间的复购率\n",
    "# 复购率 = 期间购买 2 比及以上的人数占比\n",
    "con1 = df1['order_date'] >= '2019-05-01'\n",
    "con2 = df1['order_date'] <= '2020-04-30'\n",
    "df = df1[con1 & con2]\n",
    "count_customer = len((df['customerID'].unique()))\n",
    "df = df[['customerID','order_date']]\n",
    "df_group1 = df.groupby(['customerID','order_date']).count().reset_index()\n",
    "df_group2 = df_group1.groupby(['customerID'])['order_date'].count().reset_index()\n",
    "df_group2 = df_group2[df_group2['order_date'] >= 2]\n",
    "count_customer_gn_2 = len((df_group2['customerID'].unique()))\n",
    "rate = str(round((count_customer_gn_2 / count_customer) *100,0)) + '%'\n",
    "print(f'2019-05 至 2020-04 期间的复购率为 {rate}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>store</th>\n",
       "      <th>city</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>D</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>E</td>\n",
       "      <td>C</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   store city region\n",
       "11     D    A      A\n",
       "12     E    C      B"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 给到每个城市的店铺数量和生意汇总，输出无购买记录的城市\n",
    "df_merge = pd.merge(df1,df3,on='store',how='right')\n",
    "df_merge = df_merge.fillna('')\n",
    "df_merge = df_merge[df_merge['quantity'] == '']\n",
    "df_merge = df_merge[['store','city','region']]\n",
    "df_merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "买过商品 B 和 C ，但没有买过商品 A 的顾客人数共有 1 人，平均客单价是 500.0 元/人\n"
     ]
    }
   ],
   "source": [
    "# 查找买过商品 B 和 C ，但没有买过商品 A 的顾客人数，并计算平均客单价\n",
    "df = df1[['customerID','product']]\n",
    "df_group = df.groupby(['customerID','product']).count().reset_index()\n",
    "con1 = df_group['product'] == 'A'\n",
    "con2 = df_group['product'] == 'B'\n",
    "con3 = df_group['product'] == 'C'\n",
    "df_group = df_group[con1 | con2 | con3]\n",
    "df_pivot = pd.pivot_table(df_group,index='customerID',columns='product',values='product',aggfunc=len).fillna(0).astype(int)\n",
    "con1 = df_pivot['A'] == 0\n",
    "con2 = df_pivot['B'] == 1\n",
    "con3 = df_pivot['C'] == 1\n",
    "df_filter = df_pivot[con1 & con2 & con3].reset_index()\n",
    "df_filter = df_filter[['customerID']]\n",
    "count_customer = len(df_filter['customerID'].unique())\n",
    "df_merge = pd.merge(df_filter,df1,on='customerID',how='left')\n",
    "sum_price = df_merge['price'].sum()\n",
    "price_per_customer = sum_price / count_customer\n",
    "print(f'买过商品 B 和 C ，但没有买过商品 A 的顾客人数共有 {count_customer} 人，平均客单价是 {price_per_customer} 元/人')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>city</th>\n",
       "      <th>customerID</th>\n",
       "      <th>price</th>\n",
       "      <th>组内降序排名</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>C3</td>\n",
       "      <td>750.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  city customerID  price  组内降序排名\n",
       "1    A         C3  750.0       2"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查找每个城市购买金额排名第二的客人，列出其购买城市、姓名、购买金额\n",
    "df_merge = pd.merge(df1,df3,on='store',how='right')\n",
    "df_merge = df_merge.dropna()\n",
    "df_group = df_merge.groupby(['city','customerID'])['price'].sum().reset_index()\n",
    "df_group['组内降序排名'] = df_group.groupby(['city']).rank(method='dense',ascending=False).astype(int)\n",
    "df_filter = df_group[df_group['组内降序排名'] == 2]\n",
    "df_filter"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
